﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class DBAccess
    {
        protected SqlConnection conn;
        private string strConn;

        //Open connection
        public void Connect()
        {
            strConn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

            try
            {
                if (conn == null)
                {
                    conn = new SqlConnection(strConn);
                    conn.Open();
                }
                else
                {   
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Close connection
        public void Disconnect()
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        //input query return SqlDatareader
        public SqlDataReader Returncommand(string sql)
        {
            SqlDataReader rd = null;
            SqlCommand cmd = null;

            cmd = new SqlCommand(sql, conn);
            rd = cmd.ExecuteReader();

            return rd;
        }
        
        public bool Checkstring(object obj)
        {
            float temp;
            if (float.TryParse(obj.ToString(), out temp))
                return false;
            else
                return true;
        }
        
        public string KillSqlInjectTion(string input)
        {
            input = input.Replace("'", "");
            return input;
        }

        public void ExecuteNonQuery(string sql, CommandType commandType, params object[] pars)
        {
            Connect();

            SqlCommand cmd = new SqlCommand(sql, this.conn);
            cmd.CommandType = commandType;
            SqlParameter par;
            for (int i = 0; i < pars.Length; i += 2)
            {
                object value = pars[i + 1];
                if (!Checkstring(value))
                    par = new SqlParameter(pars[i].ToString(), value);
                else
                {
                    string killstr;
                    killstr = KillSqlInjectTion(value.ToString());
                    par = new SqlParameter(pars[i].ToString(), killstr);
                }

                cmd.Parameters.Add(par);
            }

            cmd.ExecuteNonQuery();
        }

        public SqlDataReader ExecuteQuery(string sql, CommandType commandType, params object[] pars)
        {
            Connect();
            SqlCommand com = new SqlCommand(sql, this.conn);
            com.CommandType = commandType;
            SqlParameter par;
            for (int i = 0; i < pars.Length; i += 2)
            {
                object value = pars[i + 1];
                if (!Checkstring(value))
                {
                    string killstr;
                    killstr = KillSqlInjectTion(value.ToString());
                    par = new SqlParameter(pars[i].ToString(), killstr);
                }
                else
                {
                    string killstr;
                    killstr = KillSqlInjectTion(value.ToString());
                    par = new SqlParameter(pars[i].ToString(), killstr);
                }

                com.Parameters.Add(par);
            }
            return com.ExecuteReader();
        }
        
        public DataTable ExecuteQueryTable(string sql, CommandType commandType, params object[] pars)
        {
            Connect();

            SqlCommand cmd = new SqlCommand(sql, this.conn);
            cmd.CommandType = commandType;

            for (int i = 0; i < pars.Length; i += 2)
            {
                SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1]);
                cmd.Parameters.Add(par);
            }
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }

        public bool Check(string query)
        {
            this.Connect();
            SqlCommand cmd = new SqlCommand(query, conn);
            int n = int.Parse(cmd.ExecuteScalar().ToString());

            this.Disconnect();
            return (n > 0) ? true : false;
        }
    }
}
